package mww.javabean.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import mww.common.*;
import mww.javabean.entity.Score;
import mww.javabean.entity.StudentC;
import mww.javabean.entity.TeacherC;

public class StudentCDaoImpl implements StudentCDao {

	@Override
	public void addStudentC(StudentC studentC) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();// 调用ConnectionFactory组件的getConnection()方法来获得数据库连接
			String sql = "insert into StudentC(studentNo, studentName, courseNo, courseName,teacherNo,tcTeacherNo,credit,status) values(?,?,?,?,?,?,?,0)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentC.getStudentNo());
			pstmt.setString(2, studentC.getStudentName());
			pstmt.setString(3, studentC.getCourseNo());
			pstmt.setString(4, studentC.getCourseName());
			pstmt.setString(5, studentC.getTeacherNo());
			pstmt.setInt(6, studentC.getTcTeacherNo());
			pstmt.setFloat(7, studentC.getCredit());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);// 调用ResourceClose组件的close方法来关闭数据库资源
		}
	}

	@Override
	public void delStudentCById(String studentNo, String teacherNo, String courseNo, Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "delete from StudentC where studentNo=? and teacherNo=? and courseNo=? and tcTeacherNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentNo);
			pstmt.setString(2, teacherNo);
			pstmt.setString(3, courseNo);
			pstmt.setInt(4, tcTeacherNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public void delStudentCByTno(String teacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "delete from StudentC where teacherNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public void delStudentCByTnoCno(String teacherNo, String courseNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "delete from StudentC where teacherNo=? and courseNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			pstmt.setString(2, courseNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public List findStudentCByStudentNo(String studentNo) {
		StudentC sc = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示学生对象
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from studentC where studentNo= '" + studentNo + "' and status=1;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				// 将学生记录转化为学生对象
				sc = new StudentC();
				sc.setStudentNo(rs.getString("studentNo"));
				sc.setStudentName(rs.getString("studentName"));
				sc.setCourseNo(rs.getString("courseNo"));
				sc.setCourseName(rs.getString("courseName"));
				sc.setTeacherNo(rs.getString("TeacherNo"));
				sc.setTcTeacherNo(rs.getInt("tcTeacherNo"));
				sc.setCredit(rs.getFloat("Credit"));
				sc.setUsualScore(rs.getFloat("UScore"));
				sc.setLastScore(rs.getFloat("LScore"));
				sc.setTerminalScore(rs.getFloat("TScore"));
				sc.setScoreGPA(rs.getFloat("ScoreGPA"));
				list.add(sc);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}

	@Override
	public List findAllStudentById(String teacherNo, String courseNo, Integer tcTeacherNo) {
		StudentC sc = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示学生对象
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from studentC where TeacherNo=? and courseNo=? and tcTeacherNo=? order by studentNo";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			pstmt.setString(2, courseNo);
			pstmt.setInt(3, tcTeacherNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 将学生记录转化为学生对象
				sc = new StudentC();
				sc.setStudentNo(rs.getString("studentNo"));
				sc.setStudentName(rs.getString("studentName"));
				sc.setCourseNo(rs.getString("courseNo"));
				sc.setCourseName(rs.getString("courseName"));
				sc.setTeacherNo(rs.getString("TeacherNo"));
				sc.setTcTeacherNo(rs.getInt("tcTeacherNo"));
				sc.setCredit(rs.getFloat("Credit"));
				sc.setUsualScore(rs.getFloat("UScore"));
				sc.setLastScore(rs.getFloat("LScore"));
				sc.setTerminalScore(rs.getFloat("TScore"));
				sc.setScoreGPA(rs.getFloat("ScoreGPA"));
				sc.setStatus(rs.getInt("Status"));
				list.add(sc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;

	}

	@Override
	public List findAllCourseByStudentNo(String studentNo) {
		TeacherC tc = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示对象
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select studentC.courseNo,studentC.courseName,studentC.teacherNo,teacherC.teacherName,studentC.credit,teacherC.TcLocation,teacherC.TcTeacherNo,teacherC.TcTime "
					+ "from TeacherC,StudentC where studentNo = '" + studentNo
					+ "' and studentC.teacherNo=teacherC.teacherNo and studentC.courseNo=teacherC.courseNo and studentC.tcTeacherNo=teacherC.tcTeacherNo and teacherC.status=0 order by studentC.courseNo;";
			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
			System.out.println("sql=" + sql);
			while (rs.next()) {
				tc = new TeacherC();
				tc.setTeacherNo(rs.getString("studentC.teacherNo"));
				tc.setCourseNo(rs.getString("studentC.courseNo"));
				tc.setCourseName(rs.getString("studentC.courseName"));
				tc.setCredit(rs.getFloat("studentC.credit"));
				tc.setTcLocation(rs.getString("teacherC.TcLocation"));
				tc.setTcTeacherNo(rs.getInt("teacherC.TcTeacherNo"));
				tc.setTcTime(rs.getString("teacherC.TcTime"));
				tc.setTeacherName(rs.getString("teacherC.teacherName"));
				list.add(tc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;

	}

	@Override
	public Score findMaxMinAvg(String teacherNo, String courseNo, Integer tcTeacherNo) {
		Score sco = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT AVG(TScore),MAX(TScore),MIN(TScore) FROM studentc WHERE  teacherNo=? and courseNo=? and tcTeacherNo=? ;";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			pstmt.setString(2, courseNo);
			pstmt.setInt(3, tcTeacherNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				sco = new Score();
				sco.setAvgScore(rs.getFloat("AVG(TScore)"));
				sco.setMaxScore(rs.getFloat("MAX(TScore)"));
				sco.setMinScore(rs.getFloat("MIN(TScore)"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return sco;
	}

	@Override
	public void updateScore(StudentC studentC) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 计算最终成绩
		float usualScore = studentC.getUsualScore();
		float lastScore = studentC.getLastScore();
		float terminalScore = studentC.getTerminalScore();
		String studentNo = studentC.getStudentNo();
		String courseNo = studentC.getCourseNo();
		String teacherNo = studentC.getTeacherNo();
		Integer tcTeacherNo = studentC.getTcTeacherNo();
		float scoreGPA = studentC.getScoreGPA();
		Integer status = studentC.getStatus();
		try {
			conn = ConnectionFactory.getConnection();// 调用ConnectionFactory组件的getConnection()方法来获得数据库连接
			String sql = "UPDATE studentC set UScore=" + usualScore + ",Lscore=" + lastScore + ",Tscore="
					+ terminalScore + ",ScoreGPA=" + scoreGPA + ",status=" + status + " WHERE studentNo='" + studentNo
					+ "' and courseNo='" + courseNo + "' and teacherNo='" + teacherNo + "' and tcTeacherNo="
					+ tcTeacherNo + ";";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);// 调用ResourceClose组件的close方法来关闭数据库资源
		}
	}

	@Override
	public boolean checkConflict(String studentNo, String tcTime) {
		boolean b = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT studentC.studentNo,studentC.courseNo FROM teacherC,studentC "
					+ "WHERE  studentC.teacherNo=teacherC.teacherNo and studentC.courseNo=teacherC.courseNo and studentC.tcTeacherNo=teacherC.tcTeacherNo "
					+ "and studentC.studentNo='"+studentNo+"' and teacherC.tcTime='"+tcTime+"' and teacherC.status=0";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			if (rs.next()) {
				b = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return b;
	}

	@Override
	public Score calAll(String studentNo) {
		Score score = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT SUM(credit),SUM(scoreGPA),AVG(scoreGPA) FROM studentC WHERE studentNo='" + studentNo
					+ "' and scoreGPA > 0 and status=1;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				score = new Score();
				score.setSumCredit(rs.getFloat("SUM(credit)"));
				score.setSumCreditGPA(rs.getFloat("SUM(scoreGPA)"));
				score.setAvgScoreGPA(rs.getFloat("AVG(scoreGPA)"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return score;
	}

	@Override
	public boolean checkStudentCExistById(String teacherNo, String courseNo, Integer tcTeacherNo) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from studentC where teacherNo=? and courseNo=? and tcTeacherNo=?; ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			pstmt.setString(2, courseNo);
			pstmt.setInt(3, tcTeacherNo);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return flag;
	}

}
